﻿
namespace TestDeal
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Data.SqlClient;
    using System.Text;

    public class DatabaseAccessor
    {
        private DataContext database;

        public DatabaseAccessor(string connectionString)
        {
            database = new DataContext(connectionString);
        }

        public void WriteUserData(string username, string password, string email)
        {
            //
            // 1. First method to access the DB.
            //

            Table<UserData> userTable = database.GetTable<UserData>();

            UserData user1 = new UserData();
            user1.Username = username;
            user1.UserPassword = password;
            user1.Email = email;
            userTable.InsertOnSubmit(user1);

            database.SubmitChanges();
        }

        public void WriteData(string tableName, string[] tableColumns, string[,] values)
        {
            //
            // 2. Second method to access the DB.
            //

            string query = "INSERT INTO " + tableName + " (";
            int i = 0;
            int j = 0;
            for (i = 0; i < tableColumns.Length; i++)
            {
                query = query + tableColumns[i];

                if (i != tableColumns.Length - 1)
                {
                    query = query + ",";
                }
            }

            query = query + ") VALUES (";

            for (j = 0; j < values.GetLength(0); j++)
            {
                string query2 = query;

                for (i = 0; i < tableColumns.Length; i++)
                {
                    query2 = query2 + "'" + values[j, i] + "'";

                    if (i != tableColumns.Length - 1)
                    {
                        query2 = query2 + ",";
                    }
                }

                query2 = query2 + ")";

                database.ExecuteCommand(query2);
            }
        }

        public void ReadAndPrintData()
        {
            Console.WriteLine("User database info:");

            Table<UserData> userTable = database.GetTable<UserData>();

            var users = from user in userTable
                       where (user.UserId > 0)
                       select user;

            foreach (UserData user in users)
            {
                Console.WriteLine("Username: " + user.Username);
                Console.WriteLine("Email: " + user.Email);
            }
        }
    }

    [Table]
    public class RawData
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int RawId { get; set; }

        [Column]
        public string ItemName { get; set; }

        [Column]
        public string ItemDescrition { get; set; }

        [Column]
        public string ItemSource { get; set; }

        [Column]
        public float ItemPrice { get; set; }
    }

    [Table]
    public class DealData
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int DataId { get; set; }

        [Column]
        public string ItemName { get; set; }

        [Column]
        public string ItemDescrition { get; set; }

        [Column]
        public string ItemSource { get; set; }

        [Column]
        public int Category { get; set; }

        [Column]
        public float ItemPrice { get; set; }
    }

    [Table]
    public class Category
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int CategoryId { get; set; }

        [Column]
        public string Name { get; set; }
    }

    [Table]
    public class UserData
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int UserId { get; set; }

        [Column]
        public string Username { get; set; }

        [Column]
        public string UserPassword { get; set; }

        [Column]
        public string Email { get; set; }
    }

    [Table]
    public class WishList
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int WishListId { get; set; }

        [Column]
        public int UserId { get; set; }

        [Column]
        public int DealId { get; set; }
    }

}
